import os
import datetime
import xlrd
import sqlite3

from chart import draw_detail_kchart

from const import MULTI_UNITS, PROFIT_AREA_BREAKPOINT, PROFIT_LIMIT


def check_table(excel_workbook):
    """Function: Check if the table is contains transaction details.

    Args:
        excel_workbook (xlrd.book): excel file in full path

    Return:
        result (int):
            0: Normal
            1: Name of sheet is not corret
            2: Content of sheet is not corret
    """
    print("开始检查工作簿，里面包括了表格：%s" % excel_workbook.sheet_names())
    result = 0
    # 检查第3张表：持仓明细
    tbl_holding = excel_workbook.sheets()[2]
    if tbl_holding.name != "持仓明细":
        print("Excel表的第3张表的名称不是“持仓明细”！")
        result = 1
        return result
    # 抽取固定的表格内容核对
    else:
        result = check_table_heading(tbl_holding)
        if result:
            print("result=%s，异常！" % result)
            return result
        else:
            if tbl_holding.cell(8, 0).value != "持仓明细":
                result = 2
                return result
    # 检查第4张表：成交明细
    tbl_trans_details = excel_workbook.sheets()[3]
    if tbl_trans_details.name != "成交明细":
        print("Excel表的第4张表的名称不是“成交明细”！")
        result = 1
        return result
    # 抽取固定的表格内容核对
    else:
        result = check_table_heading(tbl_trans_details)
        if result:
            return result
        else:
            if tbl_trans_details.cell(8, 0).value != "成交明细":
                result = 2
                return result
    print("完成对工作表《%s》和《%s》的检查，正常。" % (tbl_holding.name,
          tbl_trans_details.name))
    return result


def check_table_heading(excel_sheet):
    """检查不同表格的表头部分，内容都是一样

    Args:
        excel_sheet (xlrd.sheet): sheet in Excel file

    Returns:
        result (int):
            0: Normal
            2: Content of sheet is not corret
    """
    # print("开始检查工作表《%s》的内容。" % excel_sheet.name)
    result = 0
    if excel_sheet.cell(1, 0).value != "基本资料":
        result = 2
        return result
    if excel_sheet.cell(2, 0).value != "客户期货期权内部资金账户":
        result = 3
        return result
    if excel_sheet.cell(2, 5).value != "交易月份":
        result = 4
        return result
    if excel_sheet.cell(3, 0).value != "客户名称":
        result = 5
        return result
    if excel_sheet.cell(3, 5).value != "查询时间":
        result = 6
        return result
    if excel_sheet.cell(4, 0).value != "期货公司名称":
        result = 7
        return result
    if excel_sheet.cell(4, 5).value != "客户证券现货内部资金账户":
        result = 8
        return result
    print("《%s》的表头格式检查完成，正常。" % excel_sheet.name)
    return result


def get_trans_details(excel_sheet, conn, account_num):
    """Function: 以列表类型返回当月全部交易明细记录.

    Args:
        excel_sheet (xlrd.sheet): 当前目录的文件名称，或者带全路径文件名称，比如'd:/test/1.xls'
        conn (sqlite3.connection): 当前打开的数据库连接
        account_num (str): 当前保证金中心账号

    Return:
        result (dict): {
            'result': True/False,
            'message': message (string),
            'transaction_list': a list with tuple values of transaction,
            ...
            }
    """

    dict_result = {}
    tbl_details = excel_sheet.sheets()[3]
    year, month = tbl_details.cell(2, 7).value.split("-")
    table_name = "transaction_%s_%s_%s" % (account_num, year, month)
    conn.execute("DROP TABLE IF EXISTS %s" % (table_name))
    conn.execute("""CREATE TABLE %s
        (id INTEGER PRIMARY KEY AUTOINCREMENT,
        datetime TEXT NOT NULL,
        contract TEXT NOT NULL,
        serial INT NOT NULL,
        direction INT NOT NULL,
        price FLOAT NOT NULL,
        amount INT NOT NULL,
        trans FLOAT NOT NULL,
        open INT NOT NULL,
        fee FLOAT NOT NULL);""" % table_name)
    # Excel表从第11行开始才是交易明细记录
    i = 10
    record_count = 0
    transaction_list = []
    while tbl_details.cell(i, 0).value != "合计":
        # print("cell(%s,0)=%s" % (i, tbl_details.cell(i, 0).value))
        # 0:交易日期
        str_date = tbl_details.cell(i, 0).value
        # 1:合约名称
        t_name = tbl_details.cell(i, 1).value
        # 2:成交序号，不加入的话，会漏除此之外所以列都一样的交易记录
        t_serial = int(tbl_details.cell(i, 2).value)
        # 3:交易时间(精确到秒)
        str_time = tbl_details.cell(i, 3).value
        t_datetime = "%s %s" % (str_date, str_time)
        # 4:买卖方向，买为1，卖为-1
        t_direction = 1 if tbl_details.cell(i, 4).value == "买" else -1
        # 5:投机/套保，没有使用
        # 6:交易价格
        t_price = float(tbl_details.cell(i, 6).value)
        # 7:交易手数
        t_amount = int(tbl_details.cell(i, 7).value)
        # 8:成交额
        t_transaction = float(tbl_details.cell(i, 8).value)
        # 9:开仓/平仓，开仓为1，平仓为-1
        t_open = 1 if tbl_details.cell(i, 9).value == "开" else -1
        # 10:手续费
        t_fee = float(tbl_details.cell(i, 10).value)
        i += 1
        tuple_str = ("(datetime, contract, serial, direction, price, "
                     "amount, trans, open, fee)")
        tuple_value = (t_datetime, t_name, t_serial, t_direction, t_price,
                       t_amount, t_transaction, t_open, t_fee)
        transaction_list.append(tuple_value)
        sql1 = ("SELECT * from %s WHERE datetime = %r "
                "AND contract = %r AND serial = %d AND direction = %d "
                "AND ABS(price - %.3f) < 0.001 AND amount = %d "
                "AND ABS(trans - %.3f) < 0.001 AND open = %d")
        sql_tuple = (table_name, t_datetime, t_name, t_serial, t_direction,
                     t_price, t_amount, t_transaction, t_open)
        sql_str = sql1 % sql_tuple
        cur = conn.cursor()
        rows = cur.execute(sql_str).fetchall()
        if len(rows) > 0:
            # print("记录已存在，不处理。查询到的 rows 如下：")
            # print(rows)
            pass
        else:
            sql_str = "INSERT INTO %s %s values %r" % (
                table_name, tuple_str, tuple_value)
            try:
                conn.execute(sql_str)
                conn.commit()
                record_count += 1
            except sqlite3.OperationalError as error:
                print("操作Sqlite3数据库出现错误，错误信息是：%s" % error)
    if record_count == 0:
        message_str = "《%s》表的 %s 条数据已在数据库中，不操作。" % (tbl_details.name, i - 10)
    else:
        message_str = "在数据表 %s 中完成插入 %s 条记录。" % (table_name, record_count)
    print(message_str)
    dict_result['result'] = True
    dict_result['message'] = message_str
    dict_result['transaction_list'] = transaction_list
    # print("返回 transaction_list 长度为：%s" % len(transaction_list))
    return dict_result


def get_holding(excel_workbook, conn, account_num):
    """Function: Get all holding details from table.

    Args:
        excel_sheet (xlrd.sheet): 当前目录的文件名称，或者带全路径文件名称，比如'd:/test/1.xls'
        conn (sqlite3.connection): 当前打开的数据库连接
        account_num (str): 当前保证金中心账号

    Return:
        result (dict): {
            'result': True/False,
            'message': message (string),
            'holding_list': a list with tuple values of transaction,
            ...
            }
    """
    dict_result = {}
    tbl_holding = excel_workbook.sheets()[2]
    year, month = tbl_holding.cell(2, 7).value.split("-")
    table_name = "holding_%s_%s_%s" % (account_num, year, month)
    sql_str = ("SELECT name FROM sqlite_master WHERE type='table' "
               "AND name='%s'" % table_name)
    conn.execute("DROP TABLE IF EXISTS %s" % table_name)
    cur = conn.cursor()
    conn.execute("""CREATE TABLE %s
                (id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT NOT NULL,
                contract TEXT NOT NULL,
                serial INT NOT NULL,
                direction INT NOT NULL,
                price FLOAT NOT NULL,
                amount INT NOT NULL,
                open INT NOT NULL);""" % table_name)
    # Excel表从第11行开始才是交易明细记录
    i = 10
    record_count = 0
    holding_list = []
    while tbl_holding.cell(i, 0).value != "合计":
        # 0:合约名称
        t_name = tbl_holding.cell(i, 0).value
        # 1:成交序号，不加入的话，会漏除此之外所以列都一样的交易记录
        t_serial = int(tbl_holding.cell(i, 1).value)
        # 2/4:交易手数，买入和卖出放在不同列，但只同时存在一个
        amount1 = tbl_holding.cell(i, 2).value
        amount2 = tbl_holding.cell(i, 4).value
        t_amount = int(amount1) if amount1 else int(amount2)
        # 3/5:交易价格，买入和卖出放在不同列，但只同时存在一个
        price1 = tbl_holding.cell(i, 3).value
        price2 = tbl_holding.cell(i, 5).value
        t_price = float(price1) if price1 else float(price2)
        # 买卖方向，买为1，卖为-1
        t_direction = 1 if price1 and amount1 else -1
        # 持仓记录全部为开仓，全部置1
        t_open = 1
        # 11:交易日期
        t_date = tbl_holding.cell(i, 11).value
        i += 1
        tuple_str = ("(contract, serial, amount, price, "
                     "direction, open, date)")
        tuple_value = (t_name, t_serial, t_amount, t_price,
                       t_direction, t_open, t_date)
        holding_list.append(tuple_value)
        sql1 = ("SELECT * from %s WHERE contract = %r AND serial = %d "
                "AND direction = %d AND ABS(price - %.3f) < 0.001 "
                "AND amount = %d AND open = %d AND date = %r")
        sql_tuple = (table_name, t_name, t_serial, t_direction, t_price,
                     t_amount, t_open, t_date)
        sql_str = sql1 % sql_tuple
        # print(sql_str)
        cur = conn.cursor()
        rows = cur.execute(sql_str).fetchall()
        if len(rows) > 0:
            # print("记录已存在，不处理。查询到的 rows 如下：")
            # print(rows)
            pass
        else:
            sql_str = "INSERT INTO %s %s values %r" % (
                table_name, tuple_str, tuple_value)
            try:
                conn.execute(sql_str)
                conn.commit()
                record_count += 1
            except sqlite3.OperationalError as error:
                print("操作Sqlite3数据库出现错误，错误信息是：%s" % error)
    if record_count == 0:
        message_str = "《%s》表的 %s 条数据已在数据库中，不操作。" % (tbl_holding.name, i - 10)
    else:
        message_str = "在数据表 %s 中完成插入 %s 条记录。" % (table_name, record_count)
    print(message_str)
    dict_result['result'] = True
    dict_result['message'] = message_str
    dict_result['holding_list'] = holding_list
    # print("返回 holding_list 长度为：%s" % len(holding_list))
    return dict_result


def get_last_month(year, month):
    """功能: 根据输入的年和月，返回上一个月的年和月

    Args:
        year (str): 4位年份数字
        month (str): 2位月份数字

    Returns:
        year (str): 4位年份数字
        month (str): 2位月份数字
    """

    int_year = int(year)
    int_month = int(month)
    last_year = int_year if int_month > 1 else (int_year - 1)
    last_month = (int_month - 1) if int_month > 1 else 12
    str_year = str(last_year)
    str_month = ("0" + str(last_month)) if last_month < 10 else str(last_month)
    return str_year, str_month


def pairing_transaction(transaction_list, conn, account_num, year, month):
    """功能: 匹配开仓和平仓记录

    Args:
        交易明细列表 (list): Abspath of file and filename
        conn (sqlite.connect): 全局数据库连接
        year (str): 4位年份数字
        month (str): 2位月份数字

    Returns:
        匹配开仓和平仓记录的列表 (list): 列表的每个元素为开仓和平仓记录组成的tuple
        待处理列表 (list): 有平仓记录但没有对应开仓记录的，放入该列表中
    """

    pairing_list = []
    # 缺少开仓记录，无法匹配的交易记录列表
    waiting_list = []
    # 采用新建列表重新排序时间
    new_trans_list = []
    # 因为根据日期时间排序，前一晚的夜盘在时间上体现为当天21:00~03:00的时间段
    # 所以需要将该段时间排到前面来
    for record in transaction_list:
        # record 结构：(t_datetime, t_name, t_serial, t_direction, t_price,
        # t_amount, t_transaction, t_open, t_fee)
        t_datetime = record[0]
        dt_str = t_datetime
        dt = datetime.datetime.strptime(dt_str, "%Y-%m-%d %H:%M:%S")
        sort_time = dt if (dt.hour > 3 and dt.hour <= 15) else (dt - datetime.timedelta(days=1))
        new_record = list(record) + [sort_time]
        new_trans_list.append(new_record)
    new_trans_list.sort(key=lambda x: x[9])
    last_year, last_month = get_last_month(year, month)
    # 先导入上个月的动态持仓表
    table_name = "dynamic_holding_%s_%s_%s" % (account_num, last_year,
                                               last_month)
    sql_str = ("SELECT name FROM sqlite_master WHERE type='table' "
               "AND name='%s'" % table_name)
    cur = conn.cursor()
    rows = cur.execute(sql_str).fetchall()
    dynamic_holding_list = []
    if len(rows) == 0:
        print("上个月的动态持仓表 %s 不存在，设为空。" % table_name)
    else:
        # 考虑到手数可能需要匹配部分平仓数量，需要修改，这里将tuple类型更换为list
        sql_str = ("SELECT * FROM %s" % table_name)
        rows = cur.execute(sql_str).fetchall()
        print("上个月的动态持仓表 %s 存在，长度为 %s。" % (table_name, len(rows)))
        # row 结构：(id, t_datetime, t_name, t_serial, t_direction, t_price,
        # t_amount, t_transaction, t_open, t_fee)
        for row in rows:
            t_datetime = row[1]
            t_datetime = record[0]
            dt_str = t_datetime
            dt = datetime.datetime.strptime(dt_str, "%Y-%m-%d %H:%M:%S")
            sort_time = dt if (dt.hour > 3 and dt.hour <= 15) else (dt - datetime.timedelta(days=1))
            new_record = list(row[1:]) + [sort_time]
            dynamic_holding_list.append(new_record)
        dynamic_holding_list.sort(key=lambda x: x[9])
    # log_file("需要处理的交易记录是：")
    # for record in new_trans_list:
    #     log_file(record)
    # log_file("目前获得的动态持仓列表是：")
    # for record in dynamic_holding_list:
    #     log_file(record)
    for i in range(len(new_trans_list)):
        record_trans = list(new_trans_list[i])
        [t_datetime, t_contract, t_serial, t_direction, t_price, t_amount,
         t_transaction, t_open, t_fee] = record_trans[:-1]
        # 是开仓记录，加入动态持仓表
        if t_open == 1:
            dynamic_holding_list.append(record_trans)
        # 是平仓记录，进行匹配
        elif t_open == -1:
            j = 0
            while j < len(dynamic_holding_list):
                record_holding = list(dynamic_holding_list[j])
                # print("record_holding=%s" % record_holding)
                (d_datetime, d_contract, d_serial, d_direction, d_price, d_amount,
                 d_transaction, d_open, d_fee) = record_holding[:-1]
                # 交易记录的成交金额和手续费也要拆分，否则统计盈亏时会有出入
                if (t_contract == d_contract and d_open == 1 and
                   d_direction == t_direction * (-1)):
                    # print("找到两条记录可以进行匹配和合并：")
                    # pprint(record_trans)
                    # pprint(record_holding)
                    log_file("找到两条记录可以进行匹配和合并：")
                    log_file("持仓记录：%s" % record_holding)
                    log_file("平仓记录：%s" % record_trans)
                    # 平仓记录手数比持仓多
                    if t_amount > d_amount:
                        # 以持仓手数为准
                        pct = d_amount / t_amount
                        new_transaction = round(pct * t_transaction, 2)
                        new_fee = round(pct * t_fee, 2)
                        record_close = [t_datetime, t_contract, t_serial,
                                        t_direction, t_price, d_amount,
                                        new_transaction, t_open, new_fee]
                        record_open = record_holding[:-1]
                        tuple_pair = (record_open, record_close)
                        pairing_list.append(tuple_pair)
                        # 持仓手数完全平掉，删除持仓记录
                        dynamic_holding_list.remove(record_holding)
                        # 待平仓记录的手数数量、成交金额、手续费对应减少，拆分等待匹配
                        t_amount = t_amount - d_amount
                        record_trans[5] = t_amount
                        record_trans[6] = round((1 - pct) * t_transaction, 2)
                        record_trans[8] = round((1 - pct) * t_fee, 2)
                        log_file("待平仓手数减少后，记录如下：%s" % record_trans)
                    # 平仓手数比持仓手数少，以平仓手数为准
                    elif t_amount < d_amount:
                        pct = t_amount / d_amount
                        new_transaction = round(pct * t_transaction, 2)
                        new_fee = round(pct * t_fee, 2)
                        record_close = [t_datetime, t_contract, t_serial,
                                        t_direction, t_price, t_amount,
                                        new_transaction, t_open, new_fee]
                        record_open = record_holding[:-1]
                        tuple_pair = (record_open, record_close)
                        pairing_list.append(tuple_pair)
                        # 持仓数量、成交金额、手续费对应减少
                        dynamic_holding_list[j][5] = d_amount - t_amount
                        pct2 = 1 - pct
                        dynamic_holding_list[j][6] = round(pct2 *
                                                           d_transaction, 2)
                        dynamic_holding_list[j][8] = round(pct2 * d_fee, 2)
                        log_file("持仓手数减少后，记录如下：%s" % dynamic_holding_list[j])
                        t_amount = 0
                        break
                    # 平仓手数与持仓手数相等，按回原记录进行
                    else:
                        record_close = [t_datetime, t_contract, t_serial,
                                        t_direction, t_price, t_amount,
                                        t_transaction, t_open, t_fee]
                        record_open = record_holding[:-1]
                        tuple_pair = (record_open, record_close)
                        pairing_list.append(tuple_pair)
                        dynamic_holding_list.remove(record_holding)
                        t_amount = 0
                        break
                # 该记录不能匹配，指针指向下一条持仓记录
                else:
                    j += 1
            # 遍历持仓记录后，还有平仓记录未平，说明有开仓记录没有补充完整，加入等待列表
            if t_amount > 0:
                record_close = [t_datetime, t_contract, t_serial, t_direction,
                                t_price, t_amount, t_transaction, t_open, t_fee]
                log_file("%s 记录未能匹配成功，加入等待列表。" % record_close)
                waiting_list.append(record_close)
                # log_file("当前待匹配列表为：")
                # for record in waiting_list:
                #     log_file(record)
        else:
            print("开仓字段数值异常：%s，跳开该交易记录：%s" % (t_open, transaction_list[i]))
    # print("匹配交易记录结束，每笔情况如下：")
    # pprint(pairing_list)
    # print("动态持仓列表如下：")
    # 匹配交易记录入库
    print("准备对 %s 条匹配好的交易记录入库。" % (len(pairing_list)))
    table_name = "pairinglist_%s_%s_%s" % (account_num, year, month)
    conn.execute("DROP TABLE IF EXISTS %s" % table_name)
    try:
        conn.execute("""CREATE TABLE %s
            (id INTEGER PRIMARY KEY AUTOINCREMENT,
            o_datetime FLOAT NOT NULL,
            o_contract TEXT NOT NULL,
            o_serial INT NOT NULL,
            o_direction INT NOT NULL,
            o_price FLOAT NOT NULL,
            o_amount INT NOT NULL,
            o_transaction FLOAT NOT NULL,
            o_open INT NOT NULL,
            o_fee FLOAT NOT NULL,
            c_datetime FLOAT NOT NULL,
            c_contract TEXT NOT NULL,
            c_serial INT NOT NULL,
            c_direction INT NOT NULL,
            c_price FLOAT NOT NULL,
            c_amount INT NOT NULL,
            c_transaction FLOAT NOT NULL,
            c_open INT NOT NULL,
            c_fee FLOAT NOT NULL);""" % table_name)
    except sqlite3.OperationalError as error:
        print("操作Sqlite3数据库出现错误，错误信息是：%s" % error)
    for record in pairing_list:
        (open_operation, close_operation) = record
        (o_datetime, o_contract, o_serial, o_direction, o_price, o_amount,
         o_transaction, o_open, o_fee) = open_operation
        (c_datetime, c_contract, c_serial, c_direction, c_price, c_amount,
         c_transaction, c_open, c_fee) = close_operation
        tuple_str = ("(o_datetime, o_contract, o_serial, o_direction, o_price, "
                     "o_amount, o_transaction, o_open, o_fee, c_datetime, "
                     "c_contract, c_serial, c_direction, c_price, c_amount, "
                     "c_transaction, c_open, c_fee)")
        tuple_value = (o_datetime, o_contract, o_serial, o_direction, o_price,
                       o_amount, o_transaction, o_open, o_fee, c_datetime,
                       c_contract, c_serial, c_direction, c_price, c_amount,
                       c_transaction, c_open, c_fee)
        sql_str = "INSERT INTO %s %s values %r" % (table_name, tuple_str,
                                                   tuple_value)
        # print(sql_str)
        try:
            conn.execute(sql_str)
            conn.commit()
        except sqlite3.OperationalError as error:
            print("对 %s 插入完整交易列表时出现错误，错误信息是：%s" % (table_name, error))
    # pprint(dynamic_holding_list)
    # 动态持仓列表入库
    print("准备对 %s 条动态持仓记录入库。" % (len(dynamic_holding_list)))
    table_name = "dynamic_holding_%s_%s_%s" % (account_num, year, month)
    conn.execute("DROP TABLE IF EXISTS %s" % table_name)
    try:
        conn.execute("""CREATE TABLE %s
            (id INTEGER PRIMARY KEY AUTOINCREMENT,
            datetime TEXT NOT NULL,
            contract TEXT NOT NULL,
            serial INT NOT NULL,
            direction INT NOT NULL,
            price FLOAT NOT NULL,
            amount INT NOT NULL,
            trans FLOAT NOT NULL,
            open INT NOT NULL,
            fee FLOAT NOT NULL);""" % table_name)
    except sqlite3.OperationalError as error:
        print("操作Sqlite3数据库出现错误，错误信息是：%s" % error)
    for record in dynamic_holding_list:
        (t_datetime, t_contract, t_serial, t_direction, t_price, t_amount,
         t_transaction, t_open, t_fee) = record[:-1]
        tuple_str = ("(datetime, contract, serial, direction, price, "
                     "amount, trans, open, fee)")
        tuple_value = (t_datetime, t_contract, t_serial, t_direction, t_price,
                       t_amount, t_transaction, t_open, t_fee)
        sql_str = "INSERT INTO %s %s values %r" % (table_name, tuple_str,
                                                   tuple_value)
        try:
            conn.execute(sql_str)
            conn.commit()
        except sqlite3.OperationalError as error:
            print("对 %s 插入动态持仓列表时出现错误，错误信息是：%s" % (table_name, error))
    # print("无法匹配的平仓交易如下：")
    # pprint(waiting_list)
    # 待匹配列表入库
    print("准备对 %s 条不能匹配的交易记录入库。" % (len(waiting_list)))
    table_name = "waitinglist_%s_%s_%s" % (account_num, year, month)
    conn.execute("DROP TABLE IF EXISTS %s" % table_name)
    try:
        conn.execute("""CREATE TABLE %s
            (id INTEGER PRIMARY KEY AUTOINCREMENT,
            datetime TEXT NOT NULL,
            contract TEXT NOT NULL,
            serial INT NOT NULL,
            direction INT NOT NULL,
            price FLOAT NOT NULL,
            amount INT NOT NULL,
            trans FLOAT NOT NULL,
            open INT NOT NULL,
            fee FLOAT NOT NULL);""" % table_name)
    except sqlite3.OperationalError as error:
        print("新建待匹配列表 %s 时出现错误，错误信息是：%s" % (table_name, error))
    for record in waiting_list:
        (t_datetime, t_contract, t_serial, t_direction, t_price, t_amount,
         t_transaction, t_open, t_fee) = record
        tuple_str = ("(datetime, contract, serial, direction, price, "
                     "amount, trans, open, fee)")
        tuple_value = (t_datetime, t_contract, t_serial, t_direction, t_price,
                       t_amount, t_transaction, t_open, t_fee)
        sql_str = "INSERT INTO %s %s values %r" % (table_name, tuple_str,
                                                   tuple_value)
        try:
            conn.execute(sql_str)
            conn.commit()
        except sqlite3.OperationalError as error:
            print("对 %s 插入待匹配列表时出现错误，错误信息是：%s" % (table_name, error))
    return pairing_list, dynamic_holding_list, waiting_list


def is_table_exist(conn, table_name):
    sql_str = ("SELECT * FROM sqlite_master WHERE type='table' "
               "AND name='%s'" % table_name)
    rows = conn.execute(sql_str).fetchall()
    # print("查询表 %s 的结果为：%s" % (table_name, len(rows)))
    # log_file("查询表 %s 的结果为：%s" % (table_name, len(rows)))
    return len(rows)


def locate_dt_60m(conn, contract, dt_str):
    """功能: 定位日期时间字符串对应的行情记录，返回索引位置.

    Args:
        conn (sqlite3.conn): 目前已打开的Sqlite数据库
        contract (str): 合约名称，如 "J1801"
        dt_str (str): 日期时间字符串，格式为：%Y-%m-%d %H:%M:%S

    Return:
        index (int): 对应记录的索引号，-1为未找到
    """
    index = -1
    table_name = "%s_60m" % contract
    cycle_str = "60分钟"
    result = is_table_exist(conn, table_name)
    if result > 0:
        sql_str = "SELECT * FROM %s" % table_name
        cur = conn.cursor()
        rows = cur.execute(sql_str).fetchall()
        dt = datetime.datetime.strptime(dt_str, "%Y-%m-%d %H:%M:%S")
        log_str = ""
        first_record = False
        if len(rows) > 0:
            # print("找到合约 %s 的60分钟记录，共 %s 条。" % (o_contract, len(rows)))
            found_date = False
            for i in range(len(rows) - 1):
                # 当前记录
                r_dt_str = rows[i][1]
                r_dt = datetime.datetime.strptime(r_dt_str,
                                                  "%Y-%m-%d %H:%M:%S")
                # 下一记录
                n_dt_str = rows[i + 1][1]
                n_dt = datetime.datetime.strptime(n_dt_str,
                                                  "%Y-%m-%d %H:%M:%S")
                if (dt.day == r_dt.day and dt.month == r_dt.month and
                        dt.year == r_dt.year):
                    if not found_date:
                        first_record = True
                    else:
                        first_record = False
                    found_date = True
                else:
                    found_date = False
                if dt.hour > 15 and dt.hour < 24:
                    dt2 = dt - datetime.timedelta(days=1)
                else:
                    dt2 = dt
                if r_dt.hour > 15 and r_dt.hour < 24:
                    r_dt2 = r_dt - datetime.timedelta(days=1)
                else:
                    r_dt2 = r_dt
                if n_dt.hour > 15 and n_dt.hour < 24:
                    n_dt2 = n_dt - datetime.timedelta(days=1)
                else:
                    n_dt2 = n_dt
                if found_date:
                    if ((dt2 >= r_dt2 and dt2 < n_dt2) or
                       (first_record and dt2 < r_dt2) or
                       i == len(rows) - 2):
                        index = i + 1
                        break
            else:
                print("没有找到匹配%s的行情记录，操作时间：%s" % (contract, dt_str))
                log_file("没有找到匹配%s的行情记录，操作时间：%s" % (contract, dt_str))
                if log_str:
                    log_file("有找到相同日期记录，整个过程如下：")
                    log_file(log_str)
    else:
        print("没有找到合约 %s 的 %s周期记录，请核对。" % (contract, cycle_str))
        log_file("没有找到合约 %s 的 %s周期记录，请核对。" % (contract, cycle_str))
    return index


def evaluate_trading(pairing_list, last_asset, now_asset, withdraw, fee,
                     risk, conn, year, month, account_num):
    """Function: According to the list of transactions, evaluate the results.

    Args:
        匹配后的交易记录 (list): 列表中每个元素为由开仓记录和平仓记录组成的tuple
        last_asset (float): 上一周期末的结算金额，作为本周期的计算基础
        now_asset (float): 本周期末的结算金额
        withdraw (float): 本周期账户出入金金额，负数为取出
        fee (float): 本周期手续费
        risk (float): 持仓风险度，单位是%
        conn (sqlite3.conn): 目前已打开的Sqlite数据库
        year (str): 当前统计年份
        month (str): 当前统计月份
        account_num (str): 用户保证金中心ID

    Return:
        None
    """

    ############### 交易指标记录 ##################
    profit_count = 0
    loss_count = 0
    profit_amount = 0
    loss_amount = 0
    profit_list = []
    loss_list = []
    profit_holding = []
    loss_holding = []
    # 盈亏金额
    pnl_amount_list = []
    # 盈亏比例，单位是%，如盈利10%
    pnl_ratio_list = []
    # 盈亏区间，根据PROFIT_AREA_BREAKPOINT分割点进行区间数量统计
    pnl_ratio_range = [0 for i in range(len(PROFIT_AREA_BREAKPOINT) + 1)]
    month_pnl_amount = 0
    month_pnl_ratio = 0
    month_max_profit = 0
    month_max_loss = 0
    month_accurcy = 0
    month_p_vs_l = 0
    month_avg_holding = 0
    month_profit_holding = []
    month_loss_holding = []
    # 将每次入库的SQL记录，最后一次执行入库
    sql_list = []
    for t in range(len(pairing_list)):
        trading = pairing_list[t]
        (open_operation, close_operation) = trading
        (o_dt, o_contract, o_serial, o_direction, o_price, o_amount,
         o_transaction, o_open, o_fee) = open_operation
        (c_dt, c_contract, c_serial, c_direction, c_price, c_amount,
         c_transaction, c_open, c_fee) = close_operation
        # 买入开仓
        if o_direction == 1:
            pass
        # 对卖方向的点进行评估，包括卖出开仓和卖出平仓，找附近的高点
        elif o_direction == -1:
            pass
        else:
            print("出现奇怪错误！买卖方向出现其他数值：%s" % o_direction)
        # ****** 开始遍历合约对应的行情 ******
        # 遍历60分钟行情记录
        log_file("开始匹配开仓记录，传入dt_str=%s" % o_dt)
        open_index = locate_dt_60m(conn, o_contract, o_dt)
        log_file("开始匹配平仓记录，传入dt_str=%s" % c_dt)
        close_index = locate_dt_60m(conn, c_contract, c_dt)
        if open_index == -1 or close_index == -1:
            log_file("合约 %s 有开仓或平仓记录未能匹配！开仓时间：%s, 平仓时间："
                     "%s。" % (o_contract, o_dt, c_dt))
            holding_cycle = -1
        else:
            holding_cycle = close_index - open_index
        table_name = "%s_60m" % o_contract
        result = is_table_exist(conn, table_name)
        if result > 0:
            sql_str = "SELECT * FROM %s" % table_name
            cur = conn.cursor()
            rows = cur.execute(sql_str).fetchall()
            # 最大浮盈
            max_floating_profit = 0
            max_floating_profit_index = -1
            # 最大浮亏
            max_floating_loss = 0
            max_floating_loss_index = -1
            # 当前浮动盈亏
            floating_pnf_max = 0
            floating_pnf_min = 0
            # 最大回撤金额
            max_wd_amount = 0
            max_wd_amount_index = -1
            # 最大回撤比例
            max_wd_pct = 0
            # 当前回撤金额
            wd_amount = 0
            # 连续盈利周期数
            profit_cycle = 0
            loss_cycle = 0
            max_profit_cycle = 0
            max_loss_cycle = 0
            # 达到PROFIT_LIMIT数值时的周期值
            profit_limit_index = -1
            multiple = get_multiple(o_contract)
            for i in range(open_index, close_index):
                record = rows[i]
                h_price = record[3]
                l_price = record[4]
                if o_direction == 1:
                    floating_pnf_max = round(((h_price - o_price) * o_amount *
                                             multiple - o_fee), 2)
                    floating_pnf_min = round(((l_price - o_price) * o_amount *
                                             multiple - o_fee), 2)
                    if h_price > o_price * (1 + PROFIT_LIMIT):
                        profit_limit_index = i - open_index
                else:
                    floating_pnf_max = round(((o_price - l_price) * o_amount *
                                             multiple - o_fee), 2)
                    floating_pnf_min = round(((o_price - h_price) * o_amount *
                                             multiple - o_fee), 2)
                    if l_price < o_price * (1 - PROFIT_LIMIT):
                        profit_limit_index = i - open_index
                if floating_pnf_min > 0:
                    profit_cycle += 1
                    loss_cycle = 0
                else:
                    loss_cycle += 1
                    profit_cycle = 0
                if profit_cycle > max_profit_cycle:
                    max_profit_cycle = profit_cycle
                if loss_cycle > max_loss_cycle:
                    max_loss_cycle = loss_cycle
                if (floating_pnf_max > 0 and
                   floating_pnf_max > max_floating_profit):
                    max_floating_profit = floating_pnf_max
                    max_floating_profit_index = i - open_index
                if (floating_pnf_min < 0 and
                   abs(floating_pnf_min) > abs(max_floating_loss)):
                    max_floating_loss = floating_pnf_min
                    max_floating_loss_index = i - open_index
                wd_amount = max_floating_profit - floating_pnf_min
                if wd_amount > max_wd_amount:
                    max_wd_amount = wd_amount
                    max_wd_amount_index = i - open_index

            # 在开仓后多少个周期开始持续盈利
            profit_cycle_start = close_index - open_index - profit_cycle
            # 开始统计该笔交易指标
            amount = ((c_price - o_price) * o_direction * o_amount * multiple
                      - o_fee - c_fee)
            if max_wd_amount != 0:
                max_wd_pct = (max_wd_amount - amount) / max_wd_amount
                max_wd_pct = round(max_wd_pct * 100, 2)
            else:
                max_wd_pct = 0
            pnl_amount = round(amount, 2)
            pnl_amount_list.append(pnl_amount)
            pnl_ratio = round(pnl_amount / last_asset * 100, 2)
            pnl_ratio_list.append(pnl_ratio)
            for i in range(len(PROFIT_AREA_BREAKPOINT)):
                if pnl_ratio < PROFIT_AREA_BREAKPOINT[i]:
                    pnl_ratio_range[i] += 1
                    break
            else:
                pnl_ratio_range[len(PROFIT_AREA_BREAKPOINT)] += 1
            if pnl_amount > 0:
                profit_count += 1
                profit_amount += pnl_amount
                profit_list.append(pnl_amount)
                profit_holding.append(holding_cycle)
            else:
                loss_count += 1
                loss_amount += pnl_amount
                loss_list.append(pnl_amount)
                loss_holding.append(holding_cycle)
            # 生成K线图
            img_url = draw_detail_kchart(table_name, account_num, year, month,
                                         rows, open_index, close_index, t)
            tbl_name = "result_detail_%s_%s_%s" % (account_num, year, month)
            tuple_str = ("(pnl_amount, pnl_ratio, max_floating_profit, "
                         "max_floating_profit_index, max_floating_loss, "
                         "max_floating_loss_index, max_wd_amount, max_wd_pct, "
                         "max_wd_amount_index, holding_cycle, "
                         "profit_cycle_start, profit_cycle, max_profit_cycle, "
                         "max_loss_cycle, profit_limit_index, img_url)")
            tuple_value = (pnl_amount, pnl_ratio, max_floating_profit,
                           max_floating_profit_index, max_floating_loss,
                           max_floating_loss_index, max_wd_amount, max_wd_pct,
                           max_wd_amount_index, holding_cycle,
                           profit_cycle_start, profit_cycle, max_profit_cycle,
                           max_loss_cycle, profit_limit_index, img_url)
            sql_str = "INSERT INTO %s %s values %r" % (tbl_name, tuple_str,
                                                       tuple_value)
            sql_list.append(sql_str)
        else:
            print("没有找到合约 %s 的记录，请核对。" % o_contract)
            log_file("没有找到合约 %s 的记录，请核对。" % o_contract)
    _asset = last_asset + withdraw
    # 月度汇总数据
    if profit_count + loss_count > 0:
        month_pnl_amount = round(sum(pnl_amount_list), 2)
        month_pnl_ratio = round(month_pnl_amount / _asset * 100, 2)
        month_max_profit = max(pnl_amount_list)
        month_max_loss = min(pnl_amount_list)
        month_avg_holding = ((sum(profit_holding) + sum(loss_holding)) /
                             (profit_count + loss_count))
        month_avg_holding = round(month_avg_holding, 1)
        if profit_count > 0:
            month_profit_holding = round(sum(profit_holding) / profit_count, 1)
        else:
            month_profit_holding = 0
        if loss_count > 0:
            month_loss_holding = round(sum(loss_holding) / loss_count, 1)
        else:
            month_loss_holding = 0
        month_accurcy = round(profit_count * 100 / (profit_count +
                                                    loss_count), 2)
        if abs(loss_amount) > 0:
            month_p_vs_l = "%.2f" % (profit_amount / abs(loss_amount))
        else:
            month_p_vs_l = "无穷大"
    # 指标数据入库
    tbl_name = "result_detail_%s_%s_%s" % (account_num, year, month)
    sql_str = "DROP TABLE IF EXISTS %s" % tbl_name
    conn.execute(sql_str)
    try:
        conn.execute("""CREATE TABLE %s
            (id INTEGER PRIMARY KEY AUTOINCREMENT,
            pnl_amount FLOAT NOT NULL,
            pnl_ratio FLOAT NOT NULL,
            max_floating_profit FLOAT NOT NULL,
            max_floating_profit_index INT NOT NULL,
            max_floating_loss FLOAT NOT NULL,
            max_floating_loss_index INT NOT NULL,
            max_wd_amount FLOAT NOT NULL,
            max_wd_pct FLOAT NOT NULL,
            max_wd_amount_index INT NOT NULL,
            holding_cycle INT NOT NULL,
            profit_cycle_start INT NOT NULL,
            profit_cycle INT NOT NULL,
            max_profit_cycle INT NOT NULL,
            max_loss_cycle INT NOT NULL,
            profit_limit_index INT NOT NULL,
            img_url TEXT);""" % tbl_name)
    except sqlite3.OperationalError as error:
        print("新建待交易记录统计表 %s 时出现错误，错误信息是：%s" % (tbl_name, error))
    for sql in sql_list:
        try:
            conn.execute(sql)
            conn.commit()
        except sqlite3.OperationalError as error:
            print("对 %s 插入交易记录统计表时出现错误，错误信息是：%s" % (tbl_name, error))
    # 打印记录指标情况
    print("%s年%s月的月度统计情况如下：" % (year, month))
    log_file("%s年%s月的月度统计情况如下：" % (year, month))
    print("上月结余：%d，本月结余：%s" % (last_asset, now_asset))
    log_file("上月结余：%d，本月结余：%s" % (last_asset, now_asset))
    # print("每笔盈亏情况：")
    # pprint(pnl_amount_list)
    print("月度盈亏金额：%.2f，收益率：%.2f%%" % (month_pnl_amount, month_pnl_ratio))
    log_file("月度盈亏金额：%.2f，收益率：%.2f%%" % (month_pnl_amount, month_pnl_ratio))
    # log_file("当月每笔盈亏情况：")
    # for i in pnl_amount_list:
    #     log_file(i)
    log_file("累计盈利金额：%.2f，累计亏损金额：%.2f" % (profit_amount, loss_amount))
    print("盈利笔数：%d，亏损笔数：%d，正确率：%.1f%%，盈亏比：%s" % (
          profit_count, loss_count, month_accurcy, month_p_vs_l))
    log_file("盈利笔数：%d，亏损笔数：%d，正确率：%.1f%%，盈亏比：%s" % (
             profit_count, loss_count, month_accurcy, month_p_vs_l))
    # 对月度汇总数据入库
    table_name = "result_summary_%s" % (account_num)
    result = is_table_exist(conn, table_name)
    if result > 0:
        sql_str = "SELECT * FROM %s WHERE year=%d AND month=%d" % (table_name,
                                                                   int(year),
                                                                   int(month))
        rows = conn.execute(sql_str).fetchall()
        if len(rows) > 0:
            sql = ("UPDATE %s SET year=%d, month=%d, last_asset=%.2f, "
                   "now_asset=%.2f, withdraw=%.2f, fee=%.2f, risk=%r, "
                   "month_avg_holding=%.1f, month_pnl_amount=%.2f, "
                   "month_pnl_ratio=%.2f, profit_amount=%.2f, loss_amount=%.2f"
                   ", profit_count=%d, loss_count=%d, month_profit_holding="
                   "%.1f, month_loss_holding=%.1f, month_max_profit=%.2f, "
                   "month_max_loss=%.2f, month_accurcy=%.2f, month_p_vs_l=%r "
                   "WHERE year=%d AND month=%d"
                   "" % (table_name, int(year), int(month), last_asset,
                         now_asset, withdraw, fee, risk, month_avg_holding,
                         month_pnl_amount, month_pnl_ratio,
                         profit_amount, loss_amount, profit_count, loss_count,
                         month_profit_holding, month_loss_holding,
                         month_max_profit, month_max_loss, month_accurcy,
                         month_p_vs_l, int(year), int(month)))
            # print(sql)
            conn.execute(sql)
            conn.commit()
        else:
            tuple_str = ("(year, month, last_asset, now_asset, withdraw, "
                         "fee, risk, month_avg_holding, month_pnl_amount, "
                         "month_pnl_ratio, profit_amount, loss_amount, "
                         "profit_count, loss_count, month_profit_holding, "
                         "month_loss_holding, month_max_profit, "
                         "month_max_loss, month_accurcy, month_p_vs_l)")
            tuple_value = (int(year), int(month), last_asset, now_asset,
                           withdraw, fee, risk, month_avg_holding,
                           month_pnl_amount, month_pnl_ratio, profit_amount,
                           loss_amount, profit_count, loss_count,
                           month_profit_holding, month_loss_holding,
                           month_max_profit, month_max_loss, month_accurcy,
                           month_p_vs_l)
            sql_str = "INSERT INTO %s %s values %r" % (table_name, tuple_str,
                                                       tuple_value)
            conn.execute(sql_str)
            conn.commit()
    else:
        conn.execute("""CREATE TABLE %s
            (id INTEGER PRIMARY KEY AUTOINCREMENT,
            year INT NOT NULL,
            month INT NOT NULL,
            last_asset FLOAT NOT NULL,
            now_asset FLOAT NOT NULL,
            withdraw FLOAT NOT NULL,
            fee FLOAT NOT NULL,
            risk TEXT NOT NULL,
            month_avg_holding FLOAT NOT NULL,
            month_pnl_amount FLOAT NOT NULL,
            month_pnl_ratio FLOAT NOT NULL,
            profit_amount FLOAT NOT NULL,
            loss_amount FLOAT NOT NULL,
            profit_count INT NOT NULL,
            loss_count INT NOT NULL,
            month_profit_holding FLOAT NOT NULL,
            month_loss_holding FLOAT NOT NULL,
            month_max_profit FLOAT NOT NULL,
            month_max_loss FLOAT NOT NULL,
            month_accurcy FLOAT NOT NULL,
            month_p_vs_l TEXT NOT NULL);""" % table_name)
        tuple_str = ("(year, month, last_asset, now_asset, withdraw, fee, "
                     "risk, month_avg_holding, month_pnl_amount, "
                     "month_pnl_ratio, profit_amount, loss_amount, "
                     "profit_count, loss_count, month_profit_holding, "
                     "month_loss_holding, month_max_profit, month_max_loss, "
                     "month_accurcy, month_p_vs_l)")
        tuple_value = (year, month, last_asset, now_asset, withdraw, fee,
                       risk, month_avg_holding, month_pnl_amount,
                       month_pnl_ratio, profit_amount, loss_amount,
                       profit_count, loss_count, month_profit_holding,
                       month_loss_holding, month_max_profit,
                       month_max_loss, month_accurcy, month_p_vs_l)
        sql_str = "INSERT INTO %s %s values %r" % (table_name, tuple_str,
                                                   tuple_value)
        conn.execute(sql_str)
        conn.commit()
        print("无表格，新建表，插入数据。")


def split_filename(fullfilename):
    filename, ext = fullfilename.split('.')
    account_num = ""
    year = ""
    month = ""
    if ext.upper() == "XLS":
        account_num, year_month = filename.split('_')
        year, month = year_month.split('-')
    return account_num, year, month


def process_logfile(account):
    basedir = os.path.abspath(os.path.dirname(__file__))
    SQLITE_DATABASE_URI = os.path.join(basedir, "data.sqlite")
    file_list = os.listdir(basedir)
    # filename = r"g:\Python\test_tdx\011002301945_2017-08.xls"
    conn = sqlite3.connect(SQLITE_DATABASE_URI)
    try:
        file_not_found = True
        for fullfilename in file_list:
            if str(account) in fullfilename:
                file_not_found = False
                print("**********************************************")
                print("开始处理 %s 日志文件..." % fullfilename)
                account_num, year, month = split_filename(fullfilename)
                excel_sheet = xlrd.open_workbook(fullfilename)
                result = check_table(excel_sheet)
                if result == 0:
                    tbl_summary = excel_sheet.sheets()[0]
                    last_asset = float("%.2f" % tbl_summary.cell(11, 2).value)
                    # 当月存取金额
                    wd_amount = float("%.2f" % tbl_summary.cell(12, 2).value)
                    now_asset = float("%.2f" % tbl_summary.cell(11, 7).value)
                    fee = float("%.2f" % tbl_summary.cell(15, 2).value)
                    risk = tbl_summary.cell(18, 7).value
                    dict_result = get_trans_details(excel_sheet, conn,
                                                    account_num)
                    # name = tbl_summary.cell(5, 2).value
                    # futures = tbl_summary.cell(6, 2).value
                    # account = Account.query.filter_by(name=name).first()
                    # if not account:
                    #     account = Account(account_num, name, futures)
                    #     db.session.add(account)
                    #     db.session.commit()
                    if "transaction_list" in dict_result:
                        transaction_list = dict_result["transaction_list"]
                        print("完成交易明细列表的提取，列表长度为：%s。" % len(
                              transaction_list))
                    dict_result = get_holding(excel_sheet, conn, account_num)
                    if "holding_list" in dict_result:
                        holding_list = dict_result["holding_list"]
                        print("完成持仓列表的提取，列表长度为：%s。" % len(holding_list))
                    if transaction_list:
                        (pairing_list, dynamic_holding_list,
                         waiting_list) = pairing_transaction(transaction_list,
                                                             conn, account_num,
                                                             year, month)
                        evaluate_trading(pairing_list, last_asset, now_asset,
                                         wd_amount, fee, risk, conn, year,
                                         month, account_num)
                    print("%s 日志文件处理完成。" % fullfilename)
                else:
                    print(("检查 %s 文件时，出现格式错误，系统无法识别，请提供中国期货市场"
                           "监控中心的月度报告表格。") % fullfilename)
        if file_not_found:
            print("在当前目录下，没有找到含有该账号数字的相关记录文件。")
    finally:
        print("主程序结束，关闭数据库并退出。")
        conn.close()


def get_multiple(contract_name):
    contract_type = ""
    for i in range(len(contract_name)):
        if not contract_name[i].isdigit():
            contract_type = contract_type + contract_name[i]
        else:
            break
    if contract_type in MULTI_UNITS:
        multiple = MULTI_UNITS[contract_type]
    else:
        print("没有找到 %s 对应的合约手数定义。" % contract_name)
        log_file("没有找到 %s 对应的合约手数定义。" % contract_name)
        multiple = 0
    return multiple


def log_file(log_str):
    date_str = datetime.datetime.now().strftime("%Y-%m-%d")
    dt_str = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    with open("%s.log" % date_str, 'a', encoding='utf-8') as f:
        full_str = "%s %s\n" % (dt_str, log_str)
        f.write(full_str)


def main():
    date_str = datetime.datetime.now().strftime("%Y-%m-%d")
    if os.path.exists("%s.log" % date_str):
        os.remove("%s.log" % date_str)
    log_file("================程序开始运行================")
    # user_input = input("请输入8位数字的证券账户代码：")
    # user_input = "28900213"   # 佘彦驰，李翠芳
    # user_input = "02301967"     # 董凯梁，徐才文
    # user_input = "02301961"   # 陈锦庭，杨俊锋
    # user_input = "02301945"   # 吴峰，李志峰
    acc_list = ["28900213", "02301945", "02301961", "02301967", "02301968"]
    for account in acc_list:
        process_logfile(account)


if __name__ == '__main__':
    main()
